using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using Model.WWW_WWW.Rss;
using IProvider.Config;
using IProvider.Interface;

namespace Provider.SqlClient
{
    public class RssProvider : IRssProvider
    {
        #region T_RssFeed
        public override List<T_RssFeedDetails> T_RssFeedSelect()
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand("select ID,RSSURL,LanguageLearn,LanguageSpeak,Description,Recommend,CatID,Active,Source,UserCount from T_RssFeed", cn);
                cmd.CommandType = CommandType.Text;
                cn.Open();
                return T_RssFeedGetEntityCollectionFromReader(ExecuteReader(cmd));
            }
        }

        public override List<T_RssFeedDetails> T_RssFeedSelect(string where, string sort, int pageIndex, int pageSize, out int recordCount)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                if (string.IsNullOrEmpty(where))
                {
                    where = "";
                }
                else
                {
                    where = " where (" +Regex.Replace(where,"where","",RegexOptions.IgnoreCase) + ")";
                    //where = " where (" + where.ToLower().Replace("where", "") + ")";
                }
                if (string.IsNullOrEmpty(sort))
                {
                    sort = "";
                }
                else
                {
                    sort = sort.ToLower().Replace("order by", "");
                }
                int lowerBound = (pageIndex - 1) * pageSize + 1;
                int upperBound = pageIndex * pageSize;
                string sql = string.Format(@"SELECT ID,RSSURL,LanguageLearn,LanguageSpeak,Description,Recommend,CatID,Active,Source,UserCount,RowNum FROM
              (
                 SELECT ID,RSSURL,LanguageLearn,LanguageSpeak,Description,Recommend,CatID,Active,Source,UserCount,ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum FROM T_RssFeed {1} 
              ) a  WHERE a.RowNum BETWEEN {2} AND {3} ORDER BY RowNum ASC;select count(*) as RecordCount from T_RssFeed {1};", sort, where, lowerBound, upperBound);
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.CommandType = CommandType.Text;
                //cmd.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                SqlDataReader dr = (SqlDataReader)ExecuteReader(cmd);
                //recordCount = (int)cmd.Parameters[0].Value;
                List<T_RssFeedDetails> list = T_RssFeedGetEntityCollectionFromReader(dr);
                recordCount = 0;
                if (dr.NextResult())
                {
                    while (dr.Read())
                    {
                        recordCount = dr.GetInt32(0);
                    }
                }
                return list;
            }
        }

        public override T_RssFeedDetails T_RssFeedSelectByPK(T_RssFeedDetails entity)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand("select ID,RSSURL,LanguageLearn,LanguageSpeak,Description,Recommend,CatID,Active,Source,UserCount from T_RssFeed where ID=@ID", cn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = entity.ID;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return T_RssFeedGetEntityFromReader(reader);
                else
                    return null;
            }
        }

        public override int T_RssFeedDelete(T_RssFeedDetails entity)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand("delete  from T_RssFeed where ID=@ID", cn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = entity.ID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return ret;
            }
        }

        public override int T_RssFeedUpdate(T_RssFeedDetails entity)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand("update T_RssFeed set RSSURL=@RSSURL,LanguageLearn=@LanguageLearn,LanguageSpeak=@LanguageSpeak,Description=@Description,Recommend=@Recommend,CatID=@CatID,Active=@Active,Source=@Source,UserCount=@UserCount where ID=@ID", cn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = entity.ID;
                cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
                cmd.Parameters.Add("@LanguageLearn", SqlDbType.VarChar).Value = entity.LanguageLearn;
                cmd.Parameters.Add("@LanguageSpeak", SqlDbType.VarChar).Value = entity.LanguageSpeak;
                cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = entity.Description;
                cmd.Parameters.Add("@Recommend", SqlDbType.Bit).Value = entity.Recommend;
                cmd.Parameters.Add("@CatID", SqlDbType.Int).Value = entity.CatID;
                cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = entity.Active;
                cmd.Parameters.Add("@Source", SqlDbType.VarChar).Value = entity.Source;
                cmd.Parameters.Add("@UserCount", SqlDbType.Int).Value = entity.UserCount;
                cn.Open();
                return ExecuteNonQuery(cmd);
            }
        }

        public override int T_RssFeedInsert(T_RssFeedDetails entity)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand("insert into T_RssFeed(RSSURL,LanguageLearn,LanguageSpeak,Description,Recommend,CatID,Active,Source,UserCount) values(@RSSURL,@LanguageLearn,@LanguageSpeak,@Description,@Recommend,@CatID,@Active,@Source,@UserCount)", cn);
                cmd.CommandType = CommandType.Text;
                //cmd.Parameters.Add("@ID", SqlDbType.Int).Value = entity.ID;
                cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
                cmd.Parameters.Add("@LanguageLearn", SqlDbType.VarChar).Value = entity.LanguageLearn;
                cmd.Parameters.Add("@LanguageSpeak", SqlDbType.VarChar).Value = entity.LanguageSpeak;
                cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = entity.Description;
                cmd.Parameters.Add("@Recommend", SqlDbType.Bit).Value = entity.Recommend;
                cmd.Parameters.Add("@CatID", SqlDbType.Int).Value = entity.CatID;
                cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = entity.Active;
                cmd.Parameters.Add("@Source", SqlDbType.VarChar).Value = entity.Source;
                cmd.Parameters.Add("@UserCount", SqlDbType.Int).Value = entity.UserCount;
                cn.Open();
                return ExecuteNonQuery(cmd);
            }
        }

        public override List<T_RssFeedDetails> T_RssFeedSelect(IDbConnection cn, IDbTransaction trans)
        {
            List<T_RssFeedDetails> list = null;
            SqlCommand cmd = new SqlCommand("select ID,RSSURL,LanguageLearn,LanguageSpeak,Description,Recommend,CatID,Active,Source,UserCount from T_RssFeed", (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            //cmd.Connection = (SqlConnection)cn;
            cmd.Transaction = (SqlTransaction)trans;
            try
            {
                SqlDataReader dr = cmd.ExecuteReader();
                list = T_RssFeedGetEntityCollectionFromReader(dr);
                dr.Close();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch { }
            }
            return list;
        }

        public override List<T_RssFeedDetails> T_RssFeedSelect(IDbConnection cn, IDbTransaction trans, string where, string sort, int pageIndex, int pageSize, out int recordCount)
        {
            List<T_RssFeedDetails> list = null;
            recordCount = -1;
            if (string.IsNullOrEmpty(where))
            {
                where = "";
            }
            else
            {
                where = " where (" + Regex.Replace(where, "where", "", RegexOptions.IgnoreCase) + ")";
                //where = " where (" + where.ToLower().Replace("where", "") + ")";
            }
            if (string.IsNullOrEmpty(sort))
            {
                sort = "";
            }
            else
            {
                sort = sort.ToLower().Replace("order by", "");
            }
            int lowerBound = (pageIndex - 1) * pageSize + 1;
            int upperBound = pageIndex * pageSize;
            string sql = string.Format(@"SELECT ID,RSSURL,LanguageLearn,LanguageSpeak,Description,Recommend,CatID,Active,Source,UserCount, RowNum  FROM
            (
               SELECT ID,RSSURL,LanguageLearn,LanguageSpeak,Description,Recommend,CatID,Active,Source,UserCount,ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum FROM T_RssFeed {1} 
            ) a
               WHERE a.RowNum BETWEEN {2} AND {3}
               ORDER BY RowNum ASC;select count(*) as RecordCount from T_RssFeed {1};", sort, where, lowerBound, upperBound);
            SqlCommand cmd = new SqlCommand(sql, (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            //cmd.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Connection = (SqlConnection)cn;
            cmd.Transaction = (SqlTransaction)trans;

            try
            {
                SqlDataReader dr = cmd.ExecuteReader();
                //recordCount = (int)cmd.Parameters[0].Value;
                list = T_RssFeedGetEntityCollectionFromReader(dr);
                recordCount = 0;
                if (dr.NextResult())
                {
                    while (dr.Read())
                    {
                        recordCount = dr.GetInt32(0);
                    }
                }
                dr.Close();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch { }
            }
            return list;
        }

        public override T_RssFeedDetails T_RssFeedSelectByPK(IDbConnection cn, IDbTransaction trans, T_RssFeedDetails entity)
        {
            T_RssFeedDetails t_RssFeedDetails = null;
            SqlCommand cmd = new SqlCommand("select ID,RSSURL,LanguageLearn,LanguageSpeak,Description,Recommend,CatID,Active,Source,UserCount from T_RssFeed where ID=@ID", (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = entity.ID;
            cmd.Connection = (SqlConnection)cn;
            cmd.Transaction = (SqlTransaction)trans;

            try
            {
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
                if (dr.Read()) t_RssFeedDetails = T_RssFeedGetEntityFromReader(dr);
                dr.Close();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch { }
            }
            return t_RssFeedDetails;
        }

        public override int T_RssFeedDelete(IDbConnection cn, IDbTransaction trans, T_RssFeedDetails entity)
        {
            SqlCommand cmd = new SqlCommand("delete  from T_RssFeed where ID=@ID", (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = entity.ID;
            //cmd.Connection = (SqlConnection)cn;
            cmd.Transaction = (SqlTransaction)trans;

            int returnValue = -2;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch
                {
                    returnValue = -3;
                }
            }
            return returnValue;
        }

        public override int T_RssFeedUpdate(IDbConnection cn, IDbTransaction trans, T_RssFeedDetails entity)
        {
            SqlCommand cmd = new SqlCommand("update T_RssFeed set ID=@ID,RSSURL=@RSSURL,LanguageLearn=@LanguageLearn,LanguageSpeak=@LanguageSpeak,Description=@Description,Recommend=@Recommend,CatID=@CatID,Active=@Active,Source=@Source,UserCount=@UserCount where ID=@ID", (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = entity.ID;
            cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
            cmd.Parameters.Add("@LanguageLearn", SqlDbType.VarChar).Value = entity.LanguageLearn;
            cmd.Parameters.Add("@LanguageSpeak", SqlDbType.VarChar).Value = entity.LanguageSpeak;
            cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = entity.Description;
            cmd.Parameters.Add("@Recommend", SqlDbType.Bit).Value = entity.Recommend;
            cmd.Parameters.Add("@CatID", SqlDbType.Int).Value = entity.CatID;
            cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = entity.Active;
            cmd.Parameters.Add("@Source", SqlDbType.VarChar).Value = entity.Source;
            cmd.Parameters.Add("@UserCount", SqlDbType.Int).Value = entity.UserCount;
            cmd.Transaction = (SqlTransaction)trans;

            int returnValue = -2;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch
                {
                    returnValue = -3;
                }
            }
            return returnValue;
        }

        public override int T_RssFeedInsert(IDbConnection cn, IDbTransaction trans, T_RssFeedDetails entity)
        {
            SqlCommand cmd = new SqlCommand("if not exists(select * from T_RssFeed where RSSURL=@RSSURL) insert into T_RssFeed(RSSURL,LanguageLearn,LanguageSpeak,Description,Recommend,CatID,Active,Source,UserCount) values(@RSSURL,@LanguageLearn,@LanguageSpeak,@Description,@Recommend,@CatID,@Active,@Source,@UserCount);", (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            //cmd.Parameters.Add("@ID", SqlDbType.Int).Value = entity.ID;
            cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
            cmd.Parameters.Add("@LanguageLearn", SqlDbType.VarChar).Value = entity.LanguageLearn;
            cmd.Parameters.Add("@LanguageSpeak", SqlDbType.VarChar).Value = entity.LanguageSpeak;
            cmd.Parameters.Add("@Description", SqlDbType.NVarChar).Value = entity.Description;
            cmd.Parameters.Add("@Recommend", SqlDbType.Bit).Value = entity.Recommend;
            cmd.Parameters.Add("@CatID", SqlDbType.Int).Value = entity.CatID;
            cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = entity.Active;
            cmd.Parameters.Add("@Source", SqlDbType.VarChar).Value = entity.Source;
            cmd.Parameters.Add("@UserCount", SqlDbType.Int).Value = entity.UserCount;

            //cmd.Connection = (SqlConnection)cn;
            cmd.Transaction = (SqlTransaction)trans;

            int returnValue = -2;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch
                {
                    returnValue = -3;
                }
            }
            return returnValue;
        }
        #endregion

        #region T_RssFeedSubscription
        public override List<T_RssFeedSubscriptionDetails> T_RssFeedSubscriptionSelect()
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand("select MemberNo,RSSURL,SortID,ShowCount,Comment from T_RssFeedSubscription", cn);
                cmd.CommandType = CommandType.Text;
                cn.Open();
                return T_RssFeedSubscriptionGetEntityCollectionFromReader(ExecuteReader(cmd));
            }
        }

        public override DataTable T_RssFeedSubscriptionSelect(string memberNo)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand(@"
declare @TargetLanguage varchar(50);
declare @TargetLanguage2 varchar(50);
declare @TargetLanguage3 varchar(50);
declare @TargetLanguage4 varchar(50);
declare @MotherTongue varchar(50);
declare @MotherTongue2 varchar(50);
declare @MotherTongue3 varchar(50);
declare @MotherTongue4 varchar(50);
SELECT @TargetLanguage = TargetLanguage,@MotherTongue=MotherTongue,@MotherTongue2=MotherTongue2,@MotherTongue3=MotherTongue3,
@MotherTongue4=MotherTongue4,@TargetLanguage2=TargetLanguage2,@TargetLanguage3=TargetLanguage3,@TargetLanguage4=TargetLanguage4
 FROM T_User_Language_Buddy_Set where MemberNo=@MemberNo;
select RSSURL,ShowCount,CatID from  (select top 9999999 * from (select rtrim(ltrim(RSSURL)) RSSURL,ShowCount,SortID,0 CatID  
from T_RssFeedSubscription where MemberNo=@MemberNo union
select rtrim(ltrim(RSSURL)) RSSURL, 9 ShowCount,'0' SortID,CatID from T_RssFeed  where CatID=1 and Active=1 and 
(LanguageLearn='All' or LanguageLearn=@TargetLanguage or LanguageLearn=@TargetLanguage2 or LanguageLearn=@TargetLanguage3 or 
LanguageLearn=@TargetLanguage4) 
and (LanguageSpeak='All' or LanguageSpeak=@MotherTongue or LanguageSpeak=@MotherTongue2 or LanguageSpeak=@MotherTongue3 or 
LanguageSpeak=@MotherTongue4)) as a order by SortID) as a", cn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = memberNo;
                cn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }

        public override string GetLanguage(string memberno)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand("SELECT MotherTongue,TargetLanguage FROM T_User_Language_Buddy_Set where MemberNo=@MemberNo;", cn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = memberno;
                cn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                string s = "English,Chinese";
                if (dt.Rows.Count == 1)
                {
                    s = string.Format("{0},{1}", dt.Rows[0][0].ToString(), dt.Rows[0][1].ToString());
                }
                return s;
            }
        }

        public override int T_RssFeedSubscriptionUpdate(string memberNo, string url, int showCount)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand("update T_RssFeedSubscription set ShowCount=@ShowCount where MemberNo=@MemberNo and RSSURL=@RSSURL", cn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = memberNo;
                cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = url;
                cmd.Parameters.Add("@ShowCount", SqlDbType.Int).Value = showCount;
                cn.Open();
                return ExecuteNonQuery(cmd);
            }
        }

        public override int T_RssFeedSubscriptionDelete(string memberNo, string url)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand(@"
                delete from T_RssFeedSubscription where MemberNo=@MemberNo and RSSURL=@RSSURL;
                if exists(select * from T_RssFeed where RSSURL=@RSSURL)
                    update T_RssFeed set UserCount = isnull(UserCount,0)-1 where RSSURL=@RSSURL;", cn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = memberNo;
                cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = url;
                cn.Open();
                return ExecuteNonQuery(cmd);
            }
        }

        public override List<T_RssFeedSubscriptionDetails> T_RssFeedSubscriptionSelect(string where, string sort, int pageIndex, int pageSize, out int recordCount)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                if (string.IsNullOrEmpty(where))
                {
                    where = "";
                }
                else
                {
                    where = " where (" + Regex.Replace(where, "where", "", RegexOptions.IgnoreCase) + ")";
                    //where = " where (" + where.ToLower().Replace("where", "") + ")";
                }
                if (string.IsNullOrEmpty(sort))
                {
                    sort = "";
                }
                else
                {
                    sort = sort.ToLower().Replace("order by", "");
                }
                int lowerBound = (pageIndex - 1) * pageSize + 1;
                int upperBound = pageIndex * pageSize;
                string sql = string.Format(@"SELECT MemberNo,RSSURL,SortID,ShowCount,Comment,RowNum FROM
              (
                 SELECT MemberNo,RSSURL,SortID,ShowCount,Comment,ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum FROM T_RssFeedSubscription {1} 
              ) a
                 WHERE a.RowNum BETWEEN {2} AND {3}
                 ORDER BY RowNum ASC;select count(*) as RecordCount from T_RssFeedSubscription {1};", sort, where, lowerBound, upperBound);
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.CommandType = CommandType.Text;
                //cmd.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                SqlDataReader dr = (SqlDataReader)ExecuteReader(cmd);
                //recordCount = (int)cmd.Parameters[0].Value;
                List<T_RssFeedSubscriptionDetails> list = T_RssFeedSubscriptionGetEntityCollectionFromReader(dr);
                recordCount = 0;
                if (dr.NextResult())
                {
                    while (dr.Read())
                    {
                        recordCount = dr.GetInt32(0);
                    }
                }
                return list;
            }
        }

        public override T_RssFeedSubscriptionDetails T_RssFeedSubscriptionSelectByPK(T_RssFeedSubscriptionDetails entity)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand("select MemberNo,RSSURL,SortID,ShowCount,Comment from T_RssFeedSubscription where MemberNo=@MemberNo and RSSURL=@RSSURL", cn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = entity.MemberNo;
                cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return T_RssFeedSubscriptionGetEntityFromReader(reader);
                else
                    return null;
            }
        }

        public override int T_RssFeedSubscriptionDelete(T_RssFeedSubscriptionDetails entity)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand(@"delete  from T_RssFeedSubscription where MemberNo=@MemberNo and RSSURL=@RSSURL;
if exists(select * from T_RssFeed where RSSURL=@RSSURL)
update T_RssFeed set UserCount = isnull(UserCount,0)-1 where RSSURL=@RSSURL;", cn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = entity.MemberNo;
                cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return ret;
            }
        }

        public override int T_RssFeedSubscriptionUpdate(T_RssFeedSubscriptionDetails entity)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand("update T_RssFeedSubscription set MemberNo=@MemberNo,RSSURL=@RSSURL,SortID=@SortID,ShowCount=@ShowCount,Comment=@Comment where MemberNo=@MemberNo and RSSURL=@RSSURL", cn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = entity.MemberNo;
                cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
                cmd.Parameters.Add("@SortID", SqlDbType.VarChar).Value = entity.SortID;
                cmd.Parameters.Add("@ShowCount", SqlDbType.Int).Value = entity.ShowCount;
                cmd.Parameters.Add("@Comment", SqlDbType.Text).Value = entity.Comment;
                cn.Open();
                return ExecuteNonQuery(cmd);
            }
        }

        public override int T_RssFeedSubscriptionInsert(T_RssFeedSubscriptionDetails entity)
        {
            using (SqlConnection cn = new SqlConnection(WWW_WWW_Rss_Config.DbConnStr))
            {
                SqlCommand cmd = new SqlCommand(@"
if not exists(select * from T_RssFeedSubscription where MemberNo=@MemberNo and RSSURL=@RSSURL)
begin
	insert into T_RssFeedSubscription(MemberNo,RSSURL,SortID,ShowCount,Comment) values(@MemberNo,@RSSURL,@SortID,@ShowCount,@Comment);
    if exists(select * from T_RssFeed where RSSURL=@RSSURL)
	    update T_RssFeed set UserCount = isnull(UserCount,0)+1 where RSSURL=@RSSURL;
end", cn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = entity.MemberNo;
                cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
                cmd.Parameters.Add("@SortID", SqlDbType.VarChar).Value = entity.SortID;
                cmd.Parameters.Add("@ShowCount", SqlDbType.Int).Value = entity.ShowCount;
                cmd.Parameters.Add("@Comment", SqlDbType.Text).Value = entity.Comment;
                cn.Open();
                return ExecuteNonQuery(cmd);
            }
        }

        public override List<T_RssFeedSubscriptionDetails> T_RssFeedSubscriptionSelect(IDbConnection cn, IDbTransaction trans)
        {
            List<T_RssFeedSubscriptionDetails> list = null;
            SqlCommand cmd = new SqlCommand("select MemberNo,RSSURL,SortID,ShowCount,Comment from T_RssFeedSubscription", (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            //cmd.Connection = (SqlConnection)cn;
            cmd.Transaction = (SqlTransaction)trans;
            try
            {
                SqlDataReader dr = cmd.ExecuteReader();
                list = T_RssFeedSubscriptionGetEntityCollectionFromReader(dr);
                dr.Close();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch { }
            }
            return list;
        }

        public override List<T_RssFeedSubscriptionDetails> T_RssFeedSubscriptionSelect(IDbConnection cn, IDbTransaction trans, string where, string sort, int pageIndex, int pageSize, out int recordCount)
        {
            List<T_RssFeedSubscriptionDetails> list = null;
            recordCount = -1;
            if (string.IsNullOrEmpty(where))
            {
                where = "";
            }
            else
            {
                where = " where (" + Regex.Replace(where, "where", "", RegexOptions.IgnoreCase) + ")";
                //where = " where (" + where.ToLower().Replace("where", "") + ")";
            }
            if (string.IsNullOrEmpty(sort))
            {
                sort = "";
            }
            else
            {
                sort = sort.ToLower().Replace("order by", "");
            }
            int lowerBound = (pageIndex - 1) * pageSize + 1;
            int upperBound = pageIndex * pageSize;
            string sql = string.Format(@"SELECT MemberNo,RSSURL,SortID,ShowCount,Comment, RowNum  FROM
            (
               SELECT MemberNo,RSSURL,SortID,ShowCount,Comment,ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum FROM T_RssFeedSubscription {1} 
            ) a
               WHERE a.RowNum BETWEEN {2} AND {3}
               ORDER BY RowNum ASC;select count(*) as RecordCount from T_RssFeedSubscription {1};", sort, where, lowerBound, upperBound);
            SqlCommand cmd = new SqlCommand(sql, (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            //cmd.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Connection = (SqlConnection)cn;
            cmd.Transaction = (SqlTransaction)trans;

            try
            {
                SqlDataReader dr = cmd.ExecuteReader();
                //recordCount = (int)cmd.Parameters[0].Value;
                list = T_RssFeedSubscriptionGetEntityCollectionFromReader(dr);
                recordCount = 0;
                if (dr.NextResult())
                {
                    while (dr.Read())
                    {
                        recordCount = dr.GetInt32(0);
                    }
                }
                dr.Close();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch { }
            }
            return list;
        }

        public override T_RssFeedSubscriptionDetails T_RssFeedSubscriptionSelectByPK(IDbConnection cn, IDbTransaction trans, T_RssFeedSubscriptionDetails entity)
        {
            T_RssFeedSubscriptionDetails t_RssFeedSubscriptionDetails = null;
            SqlCommand cmd = new SqlCommand("select MemberNo,RSSURL,SortID,ShowCount,Comment from T_RssFeedSubscription where MemberNo=@MemberNo and RSSURL=@RSSURL", (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = entity.MemberNo;
            cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
            cmd.Connection = (SqlConnection)cn;
            cmd.Transaction = (SqlTransaction)trans;

            try
            {
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
                if (dr.Read()) t_RssFeedSubscriptionDetails = T_RssFeedSubscriptionGetEntityFromReader(dr);
                dr.Close();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch { }
            }
            return t_RssFeedSubscriptionDetails;
        }

        public override int T_RssFeedSubscriptionDelete(IDbConnection cn, IDbTransaction trans, T_RssFeedSubscriptionDetails entity)
        {
            SqlCommand cmd = new SqlCommand("delete  from T_RssFeedSubscription where MemberNo=@MemberNo and RSSURL=@RSSURL;update T_RssFeed set UserCount = isnull(UserCount,0)-1 where RSSURL=@RSSURL;", (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = entity.MemberNo;
            cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
            //cmd.Connection = (SqlConnection)cn;
            cmd.Transaction = (SqlTransaction)trans;

            int returnValue = -2;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch
                {
                    returnValue = -3;
                }
            }
            return returnValue;
        }

        public override int T_RssFeedSubscriptionUpdate(IDbConnection cn, IDbTransaction trans, T_RssFeedSubscriptionDetails entity)
        {
            SqlCommand cmd = new SqlCommand("update T_RssFeedSubscription set MemberNo=@MemberNo,RSSURL=@RSSURL,SortID=@SortID,ShowCount=@ShowCount,Comment=@Comment where MemberNo=@MemberNo and RSSURL=@RSSURL", (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = entity.MemberNo;
            cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
            cmd.Parameters.Add("@SortID", SqlDbType.VarChar).Value = entity.SortID;
            cmd.Parameters.Add("@ShowCount", SqlDbType.Int).Value = entity.ShowCount;
            cmd.Parameters.Add("@Comment", SqlDbType.Text).Value = entity.Comment;
            //cmd.Connection = (SqlConnection)cn;
            cmd.Transaction = (SqlTransaction)trans;

            int returnValue = -2;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch
                {
                    returnValue = -3;
                }
            }
            return returnValue;
        }

        public override int T_RssFeedSubscriptionInsert(IDbConnection cn, IDbTransaction trans, T_RssFeedSubscriptionDetails entity)
        {
            SqlCommand cmd = new SqlCommand(@"
if not exists(select * from T_RssFeedSubscription where MemberNo=@MemberNo and RSSURL=@RSSURL)
	insert into T_RssFeedSubscription(MemberNo,RSSURL,SortID,ShowCount,Comment) values(@MemberNo,@RSSURL,@SortID,@ShowCount,@Comment);
if exists(select * from T_RssFeed where RSSURL=@RSSURL)
	update T_RssFeed set UserCount = isnull(UserCount,0)+1 where RSSURL=@RSSURL;", (SqlConnection)cn);
            cmd.CommandType = CommandType.Text;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@MemberNo", SqlDbType.Char).Value = entity.MemberNo;
            cmd.Parameters.Add("@RSSURL", SqlDbType.VarChar).Value = entity.RSSURL;
            cmd.Parameters.Add("@SortID", SqlDbType.VarChar).Value = entity.SortID;
            cmd.Parameters.Add("@ShowCount", SqlDbType.Int).Value = entity.ShowCount;
            cmd.Parameters.Add("@Comment", SqlDbType.Text).Value = entity.Comment;
            cmd.Transaction = (SqlTransaction)trans;

            int returnValue = -2;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                try
                {
                    trans.Rollback();
                }
                catch
                {
                    returnValue = -3;
                }
            }
            return returnValue;
        }
        #endregion
    }
}
